-- This is the first script which demonstrates running the loop 
-- on your own, a so-called poor man's cursor, and the faults of it.
SET NOCOUNT ON
USE Northgale
go
/* This is how the Discounts table looks like. It is a priority table. 
   That is, if there is a discount for the Customer use that. If not, 
   try the City the customer is from, and then the country. There may 
   also be a discount that applies to all customers. The discounts do 
   not accumulate, so a specific customer could have 0% even if the 
   general public has a 5% discount. 
CREATE TABLE dbo.Discounts (
    DiscountID    int IDENTITY (1,1) NOT NULL,
    CustomerID    nchar(5)     NULL,
    City          nvarchar(25) NULL,
    Country       nvarchar(15) NULL,
    Discount      decimal(5,2) NOT NULL
    CONSTRAINT pk_Discounts PRIMARY KEY (DiscountID),
    CONSTRAINT u_Discounts UNIQUE (CustomerID, City, Country),
    CONSTRAINT ckt_Discounts CHECK 
        (CASE WHEN CustomerID IS NOT NULL THEN 1 END +
         CASE WHEN City       IS NOT NULL THEN 1 END +
         CASE WHEN Country    IS NOT NULL THEN 1 END <= 1)
)
SELECT COUNT(*) FROM dbo.Discounts
*/

/* The database comes without these indexes, but if you restart the
   demo you need to drop them first.
   DROP INDEX IF EXISTS Discounts.CustomerID_ix
   DROP INDEX IF EXISTS Discounts.City_ix
   DROP INDEX IF EXISTS Discounts.Country_ix
*/


-- Setup new discount policy only for the test. (In a real world 
-- this would have been been performed before this script runs.)
EXEC setup_discounts

-- These are variables to monitor the solution.
DECLARE @rows          int = 0,
        @starttime     datetime2(3) = sysdatetime(),
        @afterms       int

-------------- Programmmer's solution start here. --------------------------
DECLARE @OrderID       int,
        @Freight       decimal(10,2),
        @CustomerID    nchar(5),
        @OldDiscount   decimal(5,2),
        @Country       nvarchar(15),
        @City          nvarchar(25),
        @NewDiscount   decimal(5,2),
        @ProductAmount decimal(10,2),
        @OrderAmount   decimal(10,2)

CREATE TABLE #tmporders(OrderID    int           NOT NULL,
                        CustomerID nchar(5)      NOT NULL,
                        Freight    decimal(10,2) NOT NULL,
                        Discount   decimal(5,2)  NOT NULL)

-- Get the orders to process. Programmer thinks it a good idea to read
-- data he needs from the Orders table into the temp table while he is
-- at it.
INSERT #tmporders(OrderID, CustomerID, Freight, Discount)
   SELECT O.OrderID, O.CustomerID, O.Freight, O.Discount
   FROM   dbo.Orders O
   WHERE  O.OrderID IN (SELECT M.OrderID FROM dbo.OrdersToModify() M)

-- Start loop.
SELECT @OrderID = 0
WHILE 1 = 1
BEGIN
   SELECT @OrderID = MIN(OrderID) 
   FROM   #tmporders 
   WHERE  OrderID > @OrderID

   IF @OrderID IS NULL
      BREAK
   
   SELECT @CustomerID = CustomerID, @Freight = Freight, 
          @OldDiscount = Discount
   FROM   #tmporders
   WHERE  OrderID = @OrderID

   SELECT @Country = Country, @City = City 
   FROM   dbo.Customers
   WHERE  CustomerID = @CustomerID

   -- Determine discount.
   IF EXISTS (SELECT * 
              FROM   dbo.Discounts 
              WHERE  CustomerID = @CustomerID)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID = @CustomerID
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  City = @City)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  City = @City
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  Country = @Country)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  Country = @Country
   END 
   ELSE IF EXISTS (SELECT *
                   FROM  Discounts
                   WHERE CustomerID IS NULL
                     AND City       IS NULL
                     AND Country    IS NULL) 
   BEGIN 
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID IS NULL
        AND  City       IS NULL
        AND  Country    IS NULL 
   END
   ELSE 
      SELECT @NewDiscount = 0

   IF @NewDiscount <> @OldDiscount
   BEGIN
      SELECT @ProductAmount = SUM(UnitPrice * Quantity)
      FROM   dbo.[Order Details]
      WHERE  OrderID = @OrderID

      SELECT @OrderAmount = @ProductAmount * (1 - @NewDiscount/100) + 
                            @Freight

      UPDATE dbo.Orders
      SET    Discount    = @NewDiscount,
             TotalAmount = @OrderAmount
      WHERE  OrderID = @OrderID
   END

   -- Here we monitor the progress.
   SELECT @rows += 1
   IF @rows % 1000 = 0
   BEGIN
      SELECT @afterms = datediff(ms, @starttime, sysdatetime())
      RAISERROR('%d rows processed in %d ms.', 0, 1, 
                @rows, @afterms) WITH NOWAIT
   END
END
SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('%d rows processed in %d ms.', 0, 1, 
          @rows, @afterms) WITH NOWAIT
go
DROP TABLE #tmporders

-- After the first execution, add PRIMARY KEY for OrderID in temp 
-- table, and see improvement in performance.

/* After second execution add these indexes for further improvement.
CREATE INDEX CustomerID_ix ON Discounts(CustomerID) INCLUDE (Discount)
CREATE INDEX City_ix ON Discounts(City) INCLUDE (Discount)
CREATE INDEX Country_ix ON Discounts(Country) INCLUDE (Discount)
*/
go
